package com.vepick.metier.requetes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.vepick.beans.Station;

/*
 * Methodes gerees par la station et communes aux clients abo ou ticket
 */
public class Requetes_station {
	/**
	 * Renvoi le nombre de velos dispo pour une une station donnee
	 */
	public static int NbVeloDispoEnStation(Connection conn, int idStation)
			throws SQLException {
		String requete = "SELECT count(*) AS nb FROM Bornette NATURAL JOIN VELO WHERE idStation ="
				+ idStation + " AND etatVelo=1 AND localisationVelo='Bornette'";
		int nb = 0;
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			nb = res.getInt("nb");
		}
		res.close();
		return nb;
	}

	public static int AfficherVeloDispoEnStation(Connection conn, int idStation) {
		int compteur = 0;
		try {
			String requete = "SELECT DISTINCT B.idVelo AS id FROM Bornette B, Station S WHERE B.idStation = S.idStation AND B.idVelo IS NOT NULL "
					+ "AND B.idStation=" + idStation;
			Statement stmt = conn.createStatement();
			ResultSet res = stmt.executeQuery(requete);
			while (res.next()) {
				System.out.println("----------------------");
				System.out.println("Velo numero : " + res.getInt("id"));
				compteur++;
			}
			System.out.println("----------------------");
			res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return compteur;
	}
	
	public static int AfficherVeloHSEnStation(Connection conn, int idStation) {
		int compteur = 0;
		try {
			String requete = "SELECT DISTINCT B.idVelo AS id FROM Bornette B, Station S, Velo V WHERE B.idStation = S.idStation AND B.idVelo IS NOT NULL "
					+ "AND B.idVelo = V.idVelo AND V.ETATVELO = 0 AND B.idStation=" + idStation;
			Statement stmt = conn.createStatement();
			ResultSet res = stmt.executeQuery(requete);
			while (res.next()) {
				System.out.println("----------------------");
				System.out.println("Velo numero : " + res.getInt("id"));
				compteur++;
			}
			System.out.println("----------------------");
			res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return compteur;
	}

	/**
	 * Renvoi le nombre de v茅los endommage pour une une station donnee
	 */
	public static int NbVeloEndommageEnStation(Connection conn, int idStation)
			throws SQLException {
		String requete = "SELECT count(*) AS nb FROM Bornette NATURAL JOIN VELO WHERE idStation ="
				+ idStation + " AND etatVelo=0 AND localisationVelo='Bornette'";
		int nb = 0;
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			nb = res.getInt("nb");
		}
		res.close();
		return nb;
	}

	/**
	 * Renvoi le nombre de bornes dispo pour une une station donnee
	 */
	public static int NbBorneDispoEnStation(Connection conn, int idStation)
			throws SQLException {
		String requete = "SELECT count(*) AS nb FROM Bornette WHERE idStation ="
				+ idStation + " AND idVelo IS NULL AND etatBornette=1";
		int nb = 0;
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			nb = res.getInt("nb");
		}
		res.close();
		return nb;
	}
	
	public static int AfficherBorneDispoEnStation(Connection conn, int idStation)
			throws SQLException {
		String requete = "SELECT * FROM Bornette WHERE idStation ="
				+ idStation + " AND idVelo IS NULL AND etatBornette=1";
		int compteur = 0;
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			System.out.println("--------------------");
			System.out.println("Bornette numéro : " + res.getInt("idBornette"));
			compteur++;
		}
		System.out.println("--------------------");
		res.close();
		return compteur;
	}

	/**
	 * recuperer la date de la derniere tache pour une une station donnee
	 */
	public static Date dateDerniereTacheStation(Connection conn, int idStation)
			throws SQLException {
		String requete = "select max(DATEFINTACHE)  as dernier_date "
				+ "from tache where idstation = ? and "
				+ "DATEFINTACHE<sysdate and tacheannule=0";
		Date date = null;
		PreparedStatement ps = conn.prepareStatement(requete);
		ps.setInt(1, idStation);

		ResultSet res = ps.executeQuery();
		while (res.next()) {
			date = res.getDate("dernier_date");
		}
		res.close();
		return date;
	}

	/**
	 * recuperer les vehicule qui vont y passer pour une une station donnee
	 */
	public static Map<Integer, String> vehiculesStation(Connection conn,
			int idStation) throws SQLException {
		String requete = "SELECT immatvehicule, MODELEVEHICULE FROM station natural "
				+ "JOIN tache natural join routines natural join vehicule "
				+ "WHERE idstation=? and DATEFINTACHE is null "
				+ "and tacheannule=0 group by immatvehicule, MODELEVEHICULE";

		PreparedStatement ps = conn.prepareStatement(requete);
		ps.setInt(1, idStation);
		ResultSet res = ps.executeQuery();

		Map<Integer, String> strList = new HashMap<Integer, String>();

		while (res.next()) {
			String str = "Num vehicule " + res.getInt("immatvehicule") + ", ";
			str = str + res.getString("MODELEVEHICULE").trim();
			strList.put(res.getInt("immatvehicule"), str);
		}
		res.close();
		return strList;

	}

	/**
	 * recuperer les taches qui sont pas faites avec idvehicule et idstation
	 */
	public static List<String> tachesVehiculeStation(Connection conn,
			int immatvehicule, int idStation) throws SQLException {
		String requete = "SELECT idtache, idtypetache, nombrevelo, SEQTACHE "
				+ "FROM tache natural join routines"
				+ " WHERE idstation=? and IMMATVEHICULE = ? and DATEFINTACHE is null and tacheannule=0";

		PreparedStatement ps = conn.prepareStatement(requete);

		ps.setInt(1, idStation);
		ps.setInt(2, immatvehicule);
		ResultSet res = ps.executeQuery();

		List<String> strList = new ArrayList<String>();

		while (res.next()) {
			PreparedStatement ps2 = conn
					.prepareStatement("select idtypetache, COMMENTAIRETACHE from typetache where idtypetache=?");
			ps2.setInt(1, res.getInt("idtypetache"));
			ResultSet res2 = ps2.executeQuery();
			String commentaire = "";
			int type = 0;
			while (res2.next()) {
				commentaire = res2.getString("COMMENTAIRETACHE");
				type = res2.getInt("idtypetache");
			}

			String str = "Tache " + res.getInt("idtache") + ", ";
			str = str + commentaire + " " + res.getInt("nombrevelo") + " "
					+ typeTacheToString(type) + ", seq:"
					+ res.getDouble("SEQTACHE");
			strList.add(str);
		}
		res.close();
		return strList;

	}

	private static String typeTacheToString(int type) {
		if (type == 1 || type == 2)
			return "velo";
		else
			return "bornette";
	}

	/**
	 * Detailler les informations d'une station en niveau de vehicule
	 * */
	public static void stationVehicules(Connection conn, int idStation)
			throws SQLException {
		StringBuffer sb = new StringBuffer();

		// Information de station
		String requete = "SELECT idStation, numRueStation, nomRueStation, cpStation, nomVilleStation "
				+ "FROM STATION where idstation=?";
		PreparedStatement ps = conn.prepareStatement(requete);
		ps.setInt(1, idStation);
		ResultSet res = ps.executeQuery();

		while (res.next()) {
			sb.append("Station ");
			sb.append(idStation);
			sb.append(", situee ");
			sb.append(res.getInt("numRueStation"));
			sb.append(" ");
			sb.append(res.getString("nomRueStation").trim());
			sb.append(" ");
			sb.append(res.getString("nomVilleStation").trim());
			sb.append(", \nLa derniere fois ou la vehicule a passe: ");
			
			Date date = dateDerniereTacheStation(conn,idStation);
			if(date==null){
				sb.append("null");
			}else{
				SimpleDateFormat f = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
				sb.append(f.format(date));
			}
		}
		res.close();
		sb.append("\n");

		// Liste de vehicule
		sb.append("Il y a les vehicules ci-dessous qui vont passer cette station:\n");
		Map<Integer, String> map = vehiculesStation(conn, idStation);

		Iterator<Map.Entry<Integer, String>> entries = map.entrySet()
				.iterator();
		while (entries.hasNext()) {
			Map.Entry<Integer, String> entry = entries.next();
			sb.append("    ==>");
			sb.append(entry.getValue());
			sb.append(", avec les taches:\n");

			// Liste de taches
			List<String> list = new ArrayList<String>();
			list = tachesVehiculeStation(conn, entry.getKey(), idStation);
			for (int i = 0; i < list.size(); i++) {
				sb.append("        ||");
				sb.append(list.get(i));
				sb.append("\n");
			}

		}

		System.out.println(sb.toString());

	}

	/**
	 * Recuperer toutes les stations
	 * */
	public static List<Station> recupererToutesStations(Connection conn)
			throws SQLException {
		List<Station> lesStations = new ArrayList<>();
		// on recupere toutes les stations de la bdd avec leur adresse
		String requete = "SELECT s.idStation, s.numRueStation, s.nomRueStation, s.cpStation, s.nomVilleStation, c.typeClassif FROM STATION s LEFT OUTER JOIN  (SELECT * FROM CLASSIFICATION WHERE sysdate between heureDebutClassif AND heureFinClassif) c ON s.idStation=c.idStation ORDER BY s.idStation ASC";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			lesStations.add(new Station(res.getInt("idStation"), res
					.getInt("numRueStation"), res.getString("nomRueStation"),
					res.getString("cpStation"), res
							.getString("nomVilleStation"), res
							.getString("typeClassif")));
		}
		res.close();
		return lesStations;
	}

	/**
	 * Liste toutes les stations de la bdd, et pour chacune donne son adresse,
	 * ses velos dispo et ses bornes dispo
	 */
	public static void afficherLesStation(Connection conn) throws SQLException {
		// on cree une liste d'objet Station(adresse, velo et borne dispo) pour
		// stocker les donnees

		// on parcours la liste pour rajouter pour chaque station, ses velos et
		// ses bornes dispo

		List<Station> lesStations = recupererToutesStations(conn);

		for (int i = 0; i < lesStations.size(); i++) {
			lesStations.get(i).setNbBornedispo(
					NbBorneDispoEnStation(conn, lesStations.get(i)
							.getIdStation()));
			lesStations.get(i).setNbVeloDispo(
					NbVeloDispoEnStation(conn, lesStations.get(i)
							.getIdStation()));
		}
		// et on affiche...!
		for (int j = 0; j < lesStations.size(); j++) {
			System.out.println("Station " + lesStations.get(j).getIdStation()
					+ ", atuellement de type "
					+ lesStations.get(j).getTypeClassif() + ", situee "
					+ lesStations.get(j).getNumRueStation() + "" + ", "
					+ lesStations.get(j).getNomRueStation().trim() + ", "
					+ lesStations.get(j).getCpStation().trim() + ", "
					+ lesStations.get(j).getNomVilleStation().trim() + ": ");
			System.out.println("    ---> "
					+ lesStations.get(j).getNbBornedispo()
					+ " borne(s) disponible(s), "
					+ lesStations.get(j).getNbVeloDispo()
					+ " velo(s) disponible(s)");
		}
		System.out.println("\n");
	}

	/**
	 * Liste toutes les stations de la bdd, et pour chacune donne son adresse,
	 * ses velos dispo et endommages, et ses bornes dispo.
	 */
	public static void afficherLesStation2(Connection conn) throws SQLException {

		List<Station> lesStations = recupererToutesStations(conn);

		for (int i = 0; i < lesStations.size(); i++) {
			lesStations.get(i).setNbBornedispo(
					NbBorneDispoEnStation(conn, lesStations.get(i)
							.getIdStation()));
			lesStations.get(i).setNbVeloDispo(
					NbVeloDispoEnStation(conn, lesStations.get(i)
							.getIdStation()));
			lesStations.get(i).setNbVeloEndommage(
					NbVeloEndommageEnStation(conn, lesStations.get(i)
							.getIdStation()));
		}

		for (int j = 0; j < lesStations.size(); j++) {
			System.out.println("Station " + lesStations.get(j).getIdStation()
					+ ", situee " + lesStations.get(j).getNumRueStation() + ""
					+ ", " + lesStations.get(j).getNomRueStation().trim()
					+ ", " + lesStations.get(j).getCpStation().trim() + ", "
					+ lesStations.get(j).getNomVilleStation().trim() + ": ");
			System.out.println("    ---> "
					+ lesStations.get(j).getNbBornedispo()
					+ " borne(s) disponible(s), "
					+ lesStations.get(j).getNbVeloDispo()
					+ " velo(s) disponible(s), "
					+ lesStations.get(j).getNbVeloEndommage()
					+ " velo(s) endommage(s)");
		}
		System.out.println("\n");
	}

}
